#!/usr/bin/python3
# -*- encoding: utf-8 -*-
'''
@File        :中棉字典检查.py
@Time        :2020/09/07 10:49:56
@Author      :He
@Software    :vsCode
'''
import pymysql
import time
import datetime
import uuid
import os
from sqlalchemy import create_engine
import pandas as pd
import datacompy
from openpyxl import load_workbook


class mysql_class:
    def __init__(self):
        self.host = 'localhost'
        self.user = 'root'
        self.passwd = '123456'
        self.db = 'zhongmian'
        self.file_name = '数据字典管理.xlsx'
        self.TENANT_CODE = 'T0021'

    def do_db(self):
        engine = create_engine("mysql+pymysql://root:" + self.passwd + "@" + self.host + ":3306/" + self.db)
        sql = ""
        sql += " SELECT                                                                            "
        sql += " 	d.DICT_BIZ AS '业务类型',                                                      "
        sql += " 	d.DICT_NAME AS '名称',                                                         "
        sql += " 	d.DICT_CODE AS '代码',                                                         "
        sql += " 	i.DICT_ITEM_NAME AS '数据项名称',                                              "
        sql += " 	i.DICT_ITEM_VAL AS '数据项值'                                                  "
        sql += " FROM                                                                              "
        sql += " 	V_TBL_SYS_DICT d                                                               "
        sql += " 	LEFT JOIN V_TBL_SYS_DICT_ITEM i ON i.dict_code = d.dict_code                   "
        sql += " WHERE                                                                             "
        sql += " 	d.DELETE_FLAG = '0'                                                            "
        sql += " 	AND i.DELETE_FLAG = '0'                                                        "

        self.df1 = pd.read_sql_query(sql, engine)
        # print(self.df1)

    def do_excel(self):
        self.df2 = pd.read_excel(self.file_name, sheet_name="业务字典")
        self.df2.drop(['序号', 'Unnamed: 6', 'Unnamed: 7'], axis=1, inplace=True)
        # print(self.df2.head())

    def getCurrentTime(self):
        return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

    def dict_compare(self):
        self.do_db()
        self.do_excel()
        compare = datacompy.Compare(self.df1, self.df2, join_columns=['业务类型', '名称', '代码', '数据项名称', '数据项值'])
        # print(compare.matches())  # 最后判断是否相等，返回 bool
        # print(compare.report())  # 打印报告详情，返回 string
        # print(compare.report(sample_count=5000))  # 打印报告详情，返回 string

        # 仅df1和仅df2记录（DataFrames）
        # print(compare.df1_unq_rows)
        # print(compare.df2_unq_rows)

        df1_unq_rows = compare.df1_unq_rows
        df2_unq_rows = compare.df2_unq_rows

        titleList = ['表名', 'SQL']
        self.dataList = []
        for index in df2_unq_rows.index:
            sqlList = []
            my_dict = {}
            my_dict['id'] = str(uuid.uuid1())
            my_dict['create_date'] = self.getCurrentTime()
            my_dict['USING_FLAG'] = '1'
            my_dict['create_user'] = 'python'
            my_dict['TENANT_CODE'] = self.TENANT_CODE

            my_dict['DICT_CODE'] = df2_unq_rows.loc[index, '代码']
            my_dict['DICT_ITEM_NAME'] = df2_unq_rows.loc[index, '数据项名称']
            my_dict['DICT_ITEM_VAL'] = df2_unq_rows.loc[index, '数据项值']

            cols = ', '.join(my_dict.keys())
            values = "','".join('%s' % v for v in my_dict.values())
            sql = "replace into %s (%s) values (%s);" % ("V_TBL_SYS_DICT_ITEM", cols, "'" + values + "'")
            sqlList.append("V_TBL_SYS_DICT_ITEM")
            sqlList.append(sql)
            self.dataList.append(sqlList)
        self.dict_compare_sql()
        df_sql = pd.DataFrame(self.dataList, columns=titleList)

        writer = pd.ExcelWriter(self.file_name, engine='openpyxl')
        writer.book = load_workbook(self.file_name)
        df1_unq_rows.to_excel(writer, sheet_name='EXCEL缺少的数据')
        df2_unq_rows.to_excel(writer, sheet_name="DB缺少的数据")
        df_sql.to_excel(writer, sheet_name="SQL")
        writer.save()
        writer.close()

    def dict_compare_sql(self):
        self.df1 = self.df1.loc[:, ['业务类型', '名称', '代码']]
        self.df2 = self.df2.loc[:, ['业务类型', '名称', '代码']]

        compare = datacompy.Compare(self.df1, self.df2, join_columns=['业务类型', '名称', '代码'])
        df2_unq_rows = compare.df2_unq_rows
        # 去重
        df2_unq_rows = df2_unq_rows.drop_duplicates(subset=None, keep='first', inplace=False)
        # print(df2_unq_rows)

        for index in df2_unq_rows.index:
            # print(df2_unq_rows.loc[index, '业务类型'])
            sqlList = []
            my_dict = {}
            my_dict['id'] = str(uuid.uuid1())
            my_dict['create_date'] = self.getCurrentTime()
            my_dict['create_user'] = 'python'
            my_dict['USING_FLAG'] = '1'
            my_dict['TENANT_CODE'] = self.TENANT_CODE

            my_dict['DICT_BIZ'] = df2_unq_rows.loc[index, '业务类型']
            my_dict['DICT_NAME'] = df2_unq_rows.loc[index, '名称']
            my_dict['DICT_CODE'] = df2_unq_rows.loc[index, '代码']

            cols = ', '.join(my_dict.keys())
            values = "','".join('%s' % v for v in my_dict.values())
            sql = "replace into %s (%s) values (%s);" % ("V_TBL_SYS_DICT", cols, "'" + values + "'")
            # self.sqlList.append(sql)
            sqlList.append("V_TBL_SYS_DICT")
            sqlList.append(sql)
            self.dataList.append(sqlList)

            # print(sql)


if __name__ == "__main__":
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    starttime = datetime.datetime.now()
    print(starttime)

    mysql_class = mysql_class()
    mysql_class.dict_compare()

    endtime = datetime.datetime.now()
    print(endtime)

    print('\n数据处理成功!所用时间为：' + str((endtime - starttime).seconds))
